﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using E.Enum;
using E.Model;
using X.DALSqlServer;

namespace E.DAL
{
    public class TeacherDAL
    {
        /// <summary>
        /// 添加教师
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddTeacher(TeacherModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@LoginID", model.LoginID),
                new SqlParameter("@Name", model.Name),
                new SqlParameter("@Pwd", model.Pwd),
                new SqlParameter("@Gender", model.Gender),
                new SqlParameter("@Email", model.Email),
                new SqlParameter("@Authority", model.Authority),
                new SqlParameter("@ForgetPwdQuestionID", model.ForgetPwdQuestionID),
                new SqlParameter("@ForgetPwdAnswer", model.ForgetPwdAnswer),
                new SqlParameter("@CertificateID", model.CertificateID)
            };
            if (!CheckExistLoginID(model.LoginID))
            {
                try
                {
                    string sql = "INSERT INTO E_Teacher(LoginID,Pwd,Name,Email,Gender,Authority,ForgetPwdQuestionID,ForgetPwdAnswer,CertificateID)VALUES(@LoginID,@Pwd,@Name,@Email,@Gender,@Authority,@ForgetPwdQuestionID,@ForgetPwdAnswer,@CertificateID); SELECT @@IDENTITY;";
                    int id = Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql, parms));
                    ArrayList sqllist = new ArrayList();
                    TeacherAreaHandle(id, model.AreaStrList, sqllist);
                    int rst = SqlHelper.ExecuteSqlTran(sqllist);
                    return rst;
                }
                catch (SqlException sqex)
                {
                    return -1;
                }
                catch (Exception ex)
                {
                    return -2;
                }
            }
            else
                return -3;
        }

        /// <summary>
        /// 更新教师
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateTeacherWithArea(TeacherModel model)
        {
            try
            {
                int rst = UpdateTeacherInfo(model);
                ArrayList sqllist = new ArrayList();
                TeacherAreaHandle(model.ID, model.AreaStrList, sqllist);
                rst += SqlHelper.ExecuteSqlTran(sqllist);
                return rst;
            }
            catch (SqlException sqex)
            {
                return -1;
            }
            catch (Exception ex)
            {
                return -2;
            }
        }
       
        /// <summary>
        /// 教师地区处理
        /// </summary>
        /// <param name="id"></param>
        /// <param name="areaid"></param>
        /// <returns></returns>
        public void TeacherAreaHandle(int id, string[] areaid, ArrayList sqllist)
        {
            sqllist.Add("DELETE FROM E_Teacher2Area WHERE TeacherID = " + id);
            foreach (string s in areaid)
            {
                sqllist.Add("INSERT INTO E_Teacher2Area(TeacherID,AreaID)VALUES(" + id + "," + s + ");");
            }
        }

        /// <summary>
        /// 检查登录ID
        /// </summary>
        /// <param name="loginid"></param>
        /// <returns></returns>
        public bool CheckExistLoginID(string loginid)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@LoginID", loginid)
            };
            string sql = "SELECT ID FROM E_Teacher WHERE LoginID = @LoginID";
            return SqlHelper.Exists(sql, parms);
        }

        /// <summary>
        /// 老师登录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public TeacherModel Login(TeacherModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@LoginID", model.LoginID),
                new SqlParameter("@Pwd", model.Pwd)
            };
            TeacherModel newModel = new TeacherModel();
            string sql = "SELECT ID,Name,Authority,A2 FROM E_Teacher WHERE LoginID = @LoginID AND Pwd = @Pwd AND DelFlag = 0";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql, parms))
            {
                if (dr.Read())
                {
                    newModel.ID = (int)dr["ID"];
                    newModel.Name = dr["Name"].ToString();
                    newModel.Authority = (EnumTeacherAuthority)dr["Authority"];
                    newModel.A2 = (int)dr["A2"];

                }
                else
                    newModel.ID = -1;

                return newModel;
            }
        }

        /// <summary>
        /// 获取老师所在地区列表
        /// </summary>
        /// <param name="teacherid"></param>
        /// <returns></returns>
        public IList<DictAreaModel> GetTeacherAreaList(int teacherid) 
        {
            string sql = "SELECT * FROM v_teacher2area WHERE TeacherID = " + teacherid;
            IList<DictAreaModel> list = new List<DictAreaModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    DictAreaModel model = new DictAreaModel();
                    model.ID = (int)dr["ID"];
                    model.Name = dr["Name"].ToString();
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 根据地区获取老师列表
        /// </summary>
        /// <param name="areaid"></param>
        /// <returns></returns>
        public Dictionary<int, string> GetTeacherByArea(int areaid)
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM v_area2teacher WHERE AreaID = " + areaid + " AND Authority = " + (int)EnumTeacherAuthority.Normal;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr["ID"], dr["Name"].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取内训老师列表
        /// </summary>
        /// <param name="projectid"></param>
        /// <returns></returns>
        public Dictionary<int, string> GetTeacherByProjectID(int projectid)
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Teacher WHERE CertificateID = " + projectid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr["ID"], dr["Name"].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 搜索教师
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public TeacherListModel GetTeacherListByCondition(SearchTeacherModel searchModel, int pageSize, int pageIndex)
        {
            TeacherListModel listModel = new TeacherListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Name", searchModel.Name),
                new SqlParameter("@LoginID", searchModel.LoginID),
                new SqlParameter("@Email", searchModel.Email),
                new SqlParameter("@AreaID", searchModel.AreaID),
                new SqlParameter("@ProjectID", searchModel.ProjectID),
                new SqlParameter("@SelfID", searchModel.SelfID),
                new SqlParameter("@DelFlag", searchModel.DelFlag),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ShortTeacherModel> list = new List<ShortTeacherModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getTeacherListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortTeacherModel shortModel = new ShortTeacherModel();
                        shortModel.ID = (int)dr["ID"];
                        shortModel.Name = dr["Name"].ToString();
                        shortModel.LoginID = dr["LoginID"].ToString();
                        shortModel.Email = dr["Email"].ToString();
                        shortModel.Gender = (bool)dr["Gender"];
                        shortModel.DelFlag = (bool)dr["DelFlag"];
                        list.Add(shortModel);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="teacherid"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public int UpdatePassword(int teacherid, string pwd)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", teacherid),
                new SqlParameter("@Pwd", pwd)
            };
            string sql = "UPDATE E_Teacher SET Pwd = @Pwd WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }     

        /// <summary>
        /// 修改密码（需要原密码）
        /// </summary>
        /// <param name="teacherid"></param>
        /// <param name="originalpwd"></param>
        /// <param name="newpwd"></param>
        /// <returns></returns>
        public int UpdatePasswordWithOriginalPwd(int teacherid, string originalpwd, string newpwd)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", teacherid),
                new SqlParameter("@OriginalPwd", originalpwd),
                new SqlParameter("@NewPwd", newpwd)
            };
            string sql = "UPDATE E_Teacher SET Pwd = @NewPwd WHERE ID = @ID AND Pwd = @OriginalPwd";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 更新信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateTeacherInfo(TeacherModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                new SqlParameter("@Name", model.Name),
                new SqlParameter("@Email", model.Email),
                new SqlParameter("@Gender", model.Gender),
                new SqlParameter("@Authority", model.Authority),
                new SqlParameter("@CertificateID", model.CertificateID)
            };
            string sql = "UPDATE E_Teacher SET Email = @Email, Name = @Name, Gender = @Gender, Authority = @Authority, CertificateID = @CertificateID WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 获取老师信息
        /// </summary>
        /// <param name="teacherid"></param>
        /// <returns></returns>
        public TeacherModel GetTeacherInfo(int teacherid)
        {
            TeacherModel model = new TeacherModel();
            string sql = "SELECT ID, LoginID,Name, Email, Gender, Authority, ForgetPwdQuestionID, ForgetPwdAnswer, CertificateID FROM E_Teacher WHERE (ID = " + teacherid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.LoginID = dr["LoginID"].ToString();
                    model.Gender = (bool)dr["Gender"];
                    model.Email = dr["Email"].ToString();
                    model.Name = dr["Name"].ToString();
                    model.ForgetPwdQuestionID = (int)dr["ForgetPwdQuestionID"];
                    model.ForgetPwdAnswer = dr["ForgetPwdAnswer"].ToString();
                    model.Authority = (EnumTeacherAuthority)dr["Authority"];
                    model.CertificateID = (int)dr["CertificateID"];
                }
                else
                    model.ID = -1;
                return model;
            }
        }
        /// <summary>
        /// 获取邮件提醒配置时间
        /// </summary>
        /// <param name="teacherid"></param>
        /// <returns></returns>
        public int GetTeacherMailDay(int teacherid)
        {
            string sql = "SELECT MailDay FROM E_Teacher WHERE (ID = " + teacherid + ")";
            int mailDay = 0;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    mailDay = (int)dr["MailDay"];
                    
                }
                return mailDay;
            }
        }
        /// <summary>
        /// 获取用户地区ID列表
        /// </summary>
        /// <param name="teacherid"></param>
        /// <returns></returns>
        public IList<int> GetTeacherAreaIDList(int teacherid)
        {
            IList<int> list = new List<int>();
            string sql = "SELECT AreaID FROM E_Teacher2Area WHERE TeacherID = " + teacherid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0]);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取教师所教班级课程列表
        /// </summary>
        /// <param name="teacherid"></param>
        /// <returns></returns>
        public IDictionary<int, string> GetTeacher2ClassCourseList(int teacherid)
        {
            IDictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT DISTINCT CourseID, CourseName FROM v_class_t WHERE TeacherID = " + teacherid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }            
        }

        /// <summary>
        /// 激活教师账号
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int ActiveTeacher(int id)
        {
            string sql = "UPDATE E_Teacher SET DelFlag = 0 WHERE ID = " + id;
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        /// <summary>
        /// 冻结教师账号
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int FreezeTeacher(int id)
        {
            string sql = "UPDATE E_Teacher SET DelFlag = 1 WHERE ID = " + id;
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }


        #region q and a

        /// <summary>
        /// 添加问题
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddQuestion(QandAModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", model.UserID),
                new SqlParameter("@ParentID", model.ParentID),
                new SqlParameter("@TeacherID", model.TeacherID),
                new SqlParameter("@Content", model.Content),
                new SqlParameter("@AddDate", model.AddDate)
            };
            string sql = "INSERT INTO E_QandA(UserID,ParentID,TeacherID,Content,AddDate)VALUES(@UserID,@ParentID,@TeacherID,@Content,@AddDate)";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 获取QnA信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public QandAModel GetQnAInfo(int id)
        {
            string sql = "SELECT ID, Content, TeacherName, AddDate, IsReply, UserName FROM v_qanda WHERE ID = " + id;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                QandAModel model = new QandAModel();
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.UserName = dr["UserName"].ToString();
                    model.TeacherName = dr["TeacherName"].ToString();
                    model.Content = dr["Content"].ToString();
                    model.AddDate = (DateTime)dr["AddDate"];
                    model.IsReply = (bool)dr["IsReply"];
                    if (model.IsReply)
                        model.ReplyInfo = GetQnAReplyInfo(model.ID);
                }
                else
                    model.ID = -1;

                return model;
            }
        }

        /// <summary>
        /// 回复答疑
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int ReplyQnA(QandAModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                new SqlParameter("@ParentID", model.ParentID),
                new SqlParameter("@TeacherID", model.TeacherID),
                new SqlParameter("@Content", model.Content),
                new SqlParameter("@AddDate", model.AddDate)
            };
            string sql = "INSERT INTO E_QandA(UserID,ParentID,TeacherID,Content,AddDate)VALUES(-1,@ParentID,@TeacherID,@Content,@AddDate);UPDATE E_QandA SET IsReply = 1, IsNew = 1 WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 获取答疑回复内容
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public IDictionary<string, DateTime> GetQnAReplyInfo(int id)
        {
            IDictionary<string, DateTime> reply = new Dictionary<string, DateTime>();
            string sql = "SELECT Content, AddDate FROM E_QandA WHERE ParentID = " + id;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    reply.Add(dr[0].ToString(), (DateTime)dr[1]);
                }
                return reply;
            }
        }

        /// <summary>
        /// 获取未回复QnA总数
        /// </summary>
        /// <param name="teacherid"></param>
        /// <returns></returns>
        public int GetUnreplyQnACount(int teacherid)
        {
            string sql = "SELECT COUNT(ID) FROM E_QandA WHERE ParentID = 0 AND IsReply = 0 AND TeacherID = " + teacherid;
            return Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql));
        }
        
        /// <summary>
        /// 获取新回复QnA总数
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int GetNewReplyQnACount(int userid)
        {
            string sql = "SELECT COUNT(ID) FROM E_QandA WHERE (UserID = " + userid + ") AND (ParentID = 0) AND (IsNew = 1)";
            return Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql));
        }

        /// <summary>
        /// 更新未查看回复QnA标识
        /// </summary>
        /// <param name="qandaid"></param>
        /// <returns></returns>
        public int UpdateNewReplyQnAFlag(int qandaid)
        {
            string sql = "UPDATE E_QandA SET IsNew = 0 WHERE ID = " + qandaid;
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        /// <summary>
        /// 获取Q&A列表（student）
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public QandAListModel GetQandAListByCondition(SearchQandAListModel searchModel, int pageSize, int pageIndex)
        {
            QandAListModel listModel = new QandAListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", searchModel.UserID),
                new SqlParameter("@UserName", searchModel.UserName),
                new SqlParameter("@TeacherName", searchModel.TeacherName),
                new SqlParameter("@TeacherID", searchModel.TeacherID),
                new SqlParameter("@FromDate", searchModel.FromDate),
                new SqlParameter("@ToDate", searchModel.ToDate),
                new SqlParameter("@IsReply", searchModel.IsReply),
                new SqlParameter("@IsNew", searchModel.IsNew),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ShortQandAModel> list = new List<ShortQandAModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getQandAListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortQandAModel shortModel = new ShortQandAModel();
                        shortModel.ID = (int)dr["ID"];
                        shortModel.TeacherName = dr["TeacherName"].ToString();
                        shortModel.UserName = dr["UserName"].ToString();
                        shortModel.AddDate = (DateTime)dr["AddDate"];
                        shortModel.IsReply = (bool)dr["IsReply"];
                        shortModel.IsNew = (bool)dr["IsNew"];
                        list.Add(shortModel);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 获取Q&A列表（admin）
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public QandAListModel GetQandAListByCondition2(SearchQandAListModel searchModel, int pageSize, int pageIndex)
        {
            QandAListModel listModel = new QandAListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserName", searchModel.UserName),
                new SqlParameter("@TeacherName", searchModel.TeacherName),
                new SqlParameter("@FromDate", searchModel.FromDate),
                new SqlParameter("@ToDate", searchModel.ToDate),
                new SqlParameter("@AreaID", searchModel.AreaID),
                new SqlParameter("@IsReply", searchModel.IsReply),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ShortQandAModel> list = new List<ShortQandAModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getQandAListByCondition2", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortQandAModel shortModel = new ShortQandAModel();
                        shortModel.ID = (int)dr["ID"];
                        shortModel.UserID = (int)dr["UserID"];
                        shortModel.UserName = dr["UserName"].ToString();
                        shortModel.TeacherName = dr["TeacherName"].ToString();
                        shortModel.AddDate = (DateTime)dr["AddDate"];
                        shortModel.IsReply = (bool)dr["IsReply"];
                        list.Add(shortModel);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 删除答疑
        /// </summary>
        /// <param name="qnaid"></param>
        /// <returns></returns>
        public int DelQnA(string[] qnaid)
        {
            string sql = "DELETE FROM E_QandA WHERE " + DALUtils.GetInCondition("ID", qnaid) + " OR " + DALUtils.GetInCondition("ParentID", qnaid);
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        #endregion

        #region 忘记密码

        /// <summary>
        /// 获取用户忘记密码信息
        /// </summary>
        /// <param name="loginid"></param>
        /// <returns></returns>
        public IDictionary<int, string> GetForgetPwdInfoByLoginID(string loginid)
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@LoginID", loginid)
            };
            string sql = "SELECT a.ID, b.Title FROM E_Teacher AS a INNER JOIN E_Dict_PwdQuestion AS b ON a.ForgetPwdQuestionID = b.ID WHERE (a.LoginID = @LoginID)";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql, parms))
            {
                if (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取用户提示问题
        /// </summary>
        /// <param name="teacherid"></param>
        /// <returns></returns>
        public PwdQuestionModel GetForgetPwdQuestionID(int teacherid)
        {
            PwdQuestionModel model = new PwdQuestionModel();
            string sql = "SELECT ID,ForgetPwdQuestionID,ForgetPwdAnswer FROM E_Teacher WHERE ID = " + teacherid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ForgetPwdQuestionID"];
                    model.TeacherID = (int)dr["ID"];
                    model.Answer = dr["ForgetPwdAnswer"].ToString();
                }
                return model;
            }
        }

        /// <summary>
        /// 检查用户忘记密码提示问题答案是否正确
        /// </summary>
        /// <param name="teacherid"></param>
        /// <param name="answer"></param>
        /// <returns></returns>
        public int CheckForgetPwdAnswer(int teacherid, string answer)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@TeacherID", teacherid),
                new SqlParameter("@Answer", answer)
            };
            string sql = "SELECT ID FROM E_Teacher WHERE ID = @TeacherID AND ForgetPwdAnswer = @Answer";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql, parms))
            {
                if (dr.Read())
                {
                    return (int)dr["ID"];
                }
                else
                    return -1;
            }
        }

        /// <summary>
        /// 获取忘记密码提示问题列表
        /// </summary>
        /// <returns></returns>
        public IDictionary<int, string> GetForgetPwdQuestionList()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Title FROM E_Dict_PwdQuestion";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取忘记密码提示问题
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetForgetPwdQuestionTitleByID(int id)
        {
            string sql = "SELECT Title FROM E_Dict_PwdQuestion WHERE ID = " + id;
            return SqlHelper.ExecuteScalarBySql(sql).ToString();
        }

        /// <summary>
        /// 更新安全信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateFortgetPwdInfo(PwdQuestionModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.TeacherID),
                new SqlParameter("@ForgetPwdQuestionID", model.ID),
                new SqlParameter("@Answer", model.Answer),
            };
            string sql = "UPDATE E_Teacher SET ForgetPwdQuestionID = @ForgetPwdQuestionID, ForgetPwdAnswer = @Answer WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        #endregion

        #region 内训

        /// <summary>
        /// 获取老师项目组信息
        /// </summary>
        /// <param name="teacherid"></param>
        /// <returns></returns>
        public Dictionary<int, string> GetTeacherProjectInfo(int teacherid)
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT a.CertificateID, b.Name FROM E_Teacher a INNER JOIN E_Dict_Certificate b ON a.CertificateID = b.ID WHERE a.ID = " + teacherid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取教师项目ID
        /// </summary>
        /// <param name="teacherid"></param>
        /// <returns></returns>
        public int GetTeacherProjectID(int teacherid)
        {
            string sql = "SELECT CertificateID FROM E_Teacher WHERE ID = " + teacherid;
            return Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql));
        }

        #endregion
    }
}
